﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CodeGenerate.Data
{
    public class ColumnUtil
    {

        /// <summary>
        /// 获取视图列信息
        /// </summary>
        /// <param name="sConnStr"></param>
        /// <param name="sViewName"></param>
        /// <returns></returns>
        public static List<Column> GetViewColumns(string sConnStr, string sViewName)
        {
            #region Sql
            string sql = @"
DECLARE @DatabaseName NVARCHAR(50)
DECLARE @OwnerName NVARCHAR(50)
DECLARE @ViewName NVARCHAR(50)

SELECT  @DatabaseName=db_name()
SET @OwnerName='dbo'
SET @ViewName='{0}'

SELECT
                      cols.COLUMN_NAME,
                      CASE
                          WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
                          ELSE cols.DATA_TYPE
                      END
                      AS DATA_TYPE,
                      cols.DATA_TYPE AS UNDERLYING_TYPE,
                      CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int) AS MAXIMUM_LENGTH,
                      cols.NUMERIC_PRECISION,
                      cols.NUMERIC_SCALE,
                      cols.IS_NULLABLE,
                      COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
                      COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC
                  FROM
                      INFORMATION_SCHEMA.COLUMNS cols
                  WHERE
                      cols.TABLE_CATALOG = @DatabaseName
                      AND cols.TABLE_SCHEMA = @OwnerName
                      AND cols.TABLE_NAME = @ViewName
                  ORDER BY
                      cols.ORDINAL_POSITION";
            #endregion


            DataView dv = SQLHelper.ExecuteDataSet(sConnStr, string.Format(sql, sViewName), CommandType.Text, null).Tables[0].DefaultView;
            List<Column> columns = new List<Column>();
            for (int i = 0; i < dv.Count; i++)
            {
                Column column = new Column();
                column.Name = dv[i]["COLUMN_NAME"].ToString();
                column.DataTypeName = dv[i]["DATA_TYPE"].ToString();
                //column.Default = dv[i]["Column_DEF"].ToString();
                column.IsNullable = dv[i]["IS_NULLABLE"].ToString() == "1" ? true : false;
                column.MaxLength = dv[i]["MAXIMUM_LENGTH"] != DBNull.Value ? int.Parse(dv[i]["MAXIMUM_LENGTH"].ToString()) : 0;
                //column.Order = int.Parse(dv[i]["Ordinal_Position"].ToString());
                column.Order = i;
                column.NumberPrecision = dv[i]["NUMERIC_PRECISION"] != DBNull.Value ? int.Parse(dv[i]["NUMERIC_PRECISION"].ToString()) : 0;
                column.DataType = TypeUtil.GetDbType(column.DataTypeName);
                column.NumberScale = (dv[i]["NUMERIC_SCALE"] == DBNull.Value) ? 0 : int.Parse(dv[i]["NUMERIC_SCALE"].ToString());
                columns.Add(column);
            }
            return columns;
        }

        public static List<Column> GetColumns(string connStr, string sTableName)
        {
            SqlParameter[] paraList = new SqlParameter[1];
            paraList[0] = new SqlParameter("@Table_Name", sTableName);

            DataSet ds = SQLHelper.ExecuteDataSet(connStr, "sp_columns", CommandType.StoredProcedure, paraList);
            DataView dv = ds.Tables[0].DefaultView;
            //排序
            dv.Sort = string.Format(" Ordinal_Position asc");

            //获取备注信息
            DataView remarkDv = GetColumnsRemark(connStr, sTableName).Tables[0].DefaultView;

            //获取主键
            string sKey = GetColumnsKey(connStr, sTableName);

            List<Column> columns = new List<Column>();
            for (int i = 0; i < dv.Count; i++)
            {
                Column column = new Column();
                column.Name = dv[i]["Column_NAME"].ToString();
                column.DataTypeName = dv[i]["Type_Name"].ToString();
                column.Default = dv[i]["Column_DEF"].ToString();
                column.IsNullable = dv[i]["NULLABLE"].ToString() == "1" ? true : false;
                column.MaxLength = int.Parse(dv[i]["Length"].ToString());
                column.Order = int.Parse(dv[i]["Ordinal_Position"].ToString());
                column.NumberPrecision = int.Parse(dv[i]["Precision"].ToString());
                //设置备注信息
                remarkDv.RowFilter = string.Format("columnName='{0}'", column.Name);
                if (remarkDv.Count > 0)
                    column.Description = remarkDv[0]["Value"] == null ? "" : remarkDv[0]["Value"].ToString();

                column.NumberScale = (dv[i]["Scale"] == DBNull.Value) ? 0 : int.Parse(dv[i]["Scale"].ToString());
                column.DataType = TypeUtil.GetDbType(column.DataTypeName);
                ///转换规则
                //column.DataType = TypeUtil.GetItem(column.StrType, EnumUtil.ConvertDataType(connStr.DataBaseType));
                column.IsPrimaryKey = column.Name.Equals(sKey, StringComparison.CurrentCultureIgnoreCase);

                columns.Add(column);

            }
            return columns;
        }

        /// <summary>
        /// 获取指定表备注信息
        /// </summary>
        /// <param name="sTableName"></param>
        /// <returns></returns>
        public static DataSet GetColumnsRemark(string connStr, string sTableName)
        {
            string sql = string.Format(@"SELECT objType,objName as columnName,name,value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '{0}', 'column', default)", sTableName);
            DataSet ds = SQLHelper.ExecuteDataSet(connStr, sql, null);
            return ds;
        }

        /// <summary>
        /// 通过表名获取主键
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <returns></returns>
        public static string GetColumnsKey(string connStr, string sTableName)
        {
            string sTemp = string.Empty;
            SqlParameter[] paraList = new SqlParameter[1];
            paraList[0] = new SqlParameter("@Table_Name", sTableName);

            SqlDataReader dr = SQLHelper.ExecuteDataReader(connStr, "sp_pkeys", CommandType.StoredProcedure, paraList);
            if (dr.Read())
            {
                sTemp = dr["COLUMN_NAME"].ToString();
            }
            dr.Close();
            return sTemp;
        }




    }
}
